/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dbHandling;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.CommissionWare;
import model.Seller;

/**
 *
 * @author Hans
 */
public class CommissionWareHandler {

    private final DatabaseConnect dbc;

    public CommissionWareHandler(DatabaseConnect dbc) {
        this.dbc = dbc;
    }

    public ArrayList<CommissionWare> getAllCommissionWares() throws SQLException {
        ArrayList<CommissionWare> list = new ArrayList<>();
        String sql = "SELECT name,phone,money,ware,cat,price, dateIn, dateOut FROM `Seller` AS s JOIN\n"
                + "(SELECT ware,name AS cat,fk_seller,price, dateIn, dateOut FROM `Category` JOIN\n"
                + "(SELECT name AS ware, fk_category, fk_seller, price, dateIn,dateOut FROM `CommissionWare` JOIN \n"
                + "(SELECT price,fk_item_ID FROM `PriceHistory` JOIN \n"
                + "(SELECT MAX(`date`) AS latestdate FROM `PriceHistory` GROUP BY fk_item_ID)\n"
                + "AS latestDates ON `date` = latestdate) \n"
                + "AS correspondingPrices ON item_ID = fk_item_ID)\n"
                + "AS ware ON `cat_ID` = `fk_category`)\n"
                + "AS com ON `phone` = `fk_seller`;";
        ResultSet rs = dbc.readFromDatabase(sql);
        while (rs.next()) {
            Seller seller = new Seller(rs.getString("name"), rs.getString("phone"), rs.getInt("money"));
            CommissionWare ware = new CommissionWare(seller, rs.getString("ware"), rs.getString("cat"), rs.getDate("dateIn").getTime(), rs.getInt("price"));
            if (rs.getDate("dateOut") != null) {
                ware.setDateOut(rs.getDate("dateOut").getTime());
            }
            list.add(ware);
        }
        return list;
    }

}
